package com.kcs.fxmargin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.kcs.fxmargin.bo.ImportDepositWithdrawBO;

public class ImportDepositWithdrawDAO {

	public ImportDepositWithdrawBO getImportDWList(ImportDepositWithdrawBO bo, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		List<ImportDepositWithdrawBO> resultList = null;
		ImportDepositWithdrawBO idw = null;
		int rownum = 0;
		
		try{
			sql = "select a.id, a.as_of_date date, c.cif_no, c.margin_acc_no, c.margin_acc_name"
				 +" , case a.type"
				 +" when 'D' then 'Deposit'"
				 +" when 'W' then 'Withdraw'"
				 +" end type_name "
				 +" , a.amount, a.ref_no, u.user_name entry_by"
				 +" from m_cust_info c, t_acc_online a left join m_users u on a.create_by = u.user_id "
				 +" where a.cust_info_id=c.id "
				 +" and c.active_status='A'"
				 +" and a.active_status='A'";
		
			if(!bo.getInStartDate().equals("")&&!bo.getInEndDate().equals("")){		//--dd/mm/yyyy
				sql += " and (a.as_of_date between convert(date,'"+bo.getInStartDate()+"',103) and convert(date,'"+bo.getInEndDate()+"',103))"; 
			}
			
			if(!bo.getInACName().equals("")){
				sql += " and UPPER(c.margin_acc_name) like UPPER('"+bo.getInACName()+"')";
			}
				 
			sql +=" union all"
				 +" select a.id, a.trans_date date, c.cif_no, c.margin_acc_no, c.margin_acc_name"
				 +" , case a.type "
				 +" when 'D' then 'Deposit'"
				 +" when 'W' then 'Withdraw'"
				 +" end type_name "
				 +" , a.amount, CAST(a.seq AS VARCHAR) ref_no, a.create_by entry_by"
				 +" from t_acc_tran a, t_acc_pos p left join m_cust_info c on p.margin_acc_no=c.margin_acc_no"
				 +" where a.acc_pos_id=p.id"
				 +" and a.active_status='A'"
				 +" and p.active_status='A'";
		
		    if(!bo.getInStartDate().equals("") && !bo.getInEndDate().equals("")){
				 sql += "and a.trans_date between convert(date,'"+bo.getInStartDate()+"',103) and convert(date,'"+bo.getInEndDate()+"',103)"; //--dd/mm/yyyy
		    }
			
			if(!bo.getInACName().equals("")){
				sql += " and UPPER(c.margin_acc_name) like UPPER('"+bo.getInACName()+"')";
			}
			
			sql +=" order by date, ref_no";
			
			
			System.out.println(">param1: "+bo.getInStartDate());
			System.out.println(">param2: "+bo.getInEndDate());
			System.out.println(">param3: "+bo.getInACName());
			System.out.println(sql);
			
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			if(rs != null){
				resultList = new ArrayList<ImportDepositWithdrawBO>();
				
				while(rs.next()){
					idw = new ImportDepositWithdrawBO();
					idw.setId(rs.getString("id"));
					idw.setDate(rs.getString("date"));
					idw.setCifNo(rs.getString("cif_no"));
					idw.setAcNo(rs.getString("margin_acc_no"));
					idw.setAcName(rs.getString("margin_acc_name"));
					idw.setTypeName(rs.getString("type_name"));
					idw.setAmount(rs.getString("amount"));
					idw.setRefNo(rs.getString("ref_no"));
					idw.setEntryBy(rs.getString("entry_by"));
					idw.setRowNum(++rownum);
					
					System.out.println(">>> [result] : "+idw.getId());

					resultList.add(idw);
				}
				
				bo.setImportDWList(resultList);
			}
			
			
		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return bo;
	}
}
